﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Paros.DAL;
using Paros.Domain;

namespace Paros.BLL
{
    public class EntryMgr
    {
        const string SqlSelectEntriesByCategory = "SELECT * FROM Entries WHERE Category = @categoryId";
        const string SqlSelectEntryById = "SELECT * FROM Entries WHERE ID = @id";
        const string SqlSelectLastEntry = "SELECT * FROM Entries ORDER BY ID DESC LIMIT 1";
        const string SqlUpdateEntry = "UPDATE Entries SET Name=@title, Content=@content, Category = @category, ModifiedTime=@modifiedTime, ModifiedUser=@modifiedUser WHERE ID=@id";
        const string SqlDeleteEntry = "DELETE FROM Entries WHERE ID=@id";
        const string SqlInsertEntry = "INSERT INTO Entries(Name, Category, Content, ModifiedTime, ModifiedUser) VALUES(@title, @category, @content, @modifiedTime, @modifiedUser)";
        DBFactory factory;
        public EntryMgr()
        {
            factory = new DBFactory();
        }

        public IList<EntryItem> GetEntriesByCategory(int categoryId)
        {
            IList<EntryItem> list = new List<EntryItem>();
            CategoryMgr categoryMgr = new CategoryMgr();
            DbParameter[] paras = { DBFactory.Instance.CreateParameter() };
            paras[0].ParameterName = "@categoryId";
            paras[0].Value = categoryId;
            using (DbDataReader reader = factory.ExecuteReader(SqlSelectEntriesByCategory, paras))
            {
                while (reader.Read())
                {
                    EntryItem item = new EntryItem();
                    item.ID = Convert.ToInt32(reader["ID"]);
                    item.Title = reader["Name"].ToString();
                    item.Category = categoryMgr.GetItem(categoryMgr.GetCategory(Convert.ToInt32(reader["Category"])));
                    item.ModifiedUser = reader["ModifiedUser"].ToString();
                    DateTime d = new DateTime();
                    DateTime.TryParse(reader["ModifiedTime"].ToString(), out d);
                    item.ModifiedTime = d;
                    item.Content = reader["Content"].ToString();
                    list.Add(item);
                }
            }

            return list;
        }

        public EntryItem GetEntryById(int entryId)
        {
            EntryItem item = new EntryItem();
            CategoryMgr categoryMgr = new CategoryMgr();
            DbParameter[] paras = { DBFactory.Instance.CreateParameter() };
            paras[0].ParameterName = "@id";
            paras[0].Value = entryId;
            using (DbDataReader reader = factory.ExecuteReader(SqlSelectEntryById, paras))
            {
                while (reader.Read())
                {
                    item.ID = Convert.ToInt32(reader["ID"]);
                    item.Title = reader["Name"].ToString();
                    item.Category = categoryMgr.GetItem(categoryMgr.GetCategory(Convert.ToInt32(reader["Category"])));
                    item.ModifiedUser = reader["ModifiedUser"].ToString();
                    DateTime d = new DateTime();
                    DateTime.TryParse(reader["ModifiedTime"].ToString(), out d);
                    item.ModifiedTime = d;
                    item.Content = reader["Content"].ToString();
                }
            }

            return item;
        }


        public EntryItem GetLastEntry()
        {
            EntryItem item = new EntryItem();
            CategoryMgr categoryMgr = new CategoryMgr();

            using (DbDataReader reader = factory.ExecuteReader(SqlSelectLastEntry, null))
            {
                while (reader.Read())
                {
                    item.ID = Convert.ToInt32(reader["ID"]);
                    item.Title = reader["Name"].ToString();
                    item.Category = categoryMgr.GetItem(categoryMgr.GetCategory(Convert.ToInt32(reader["Category"])));
                    item.ModifiedUser = reader["ModifiedUser"].ToString();
                    DateTime d = new DateTime();
                    DateTime.TryParse(reader["ModifiedTime"].ToString(), out d);
                    item.ModifiedTime = d;
                    item.Content = reader["Content"].ToString();
                }
            }

            return item;
        }

        public int SaveEntry(EntryItem item)
        {
            DbParameter[] paras = { DBFactory.Instance.CreateParameter(), 
                                      DBFactory.Instance.CreateParameter(), 
                                      DBFactory.Instance.CreateParameter(),
                                  DBFactory.Instance.CreateParameter(), 
                                  DBFactory.Instance.CreateParameter()};
            paras[0].ParameterName = "@category";
            paras[0].Value = item.Category.CategoryID;

            paras[1].ParameterName = "@title";
            paras[1].Value = item.Title;

            paras[2].ParameterName = "@content";
            paras[2].Value = item.Content;

            paras[3].ParameterName = "@modifiedUser";
            paras[3].Value = item.ModifiedUser;

            paras[4].ParameterName = "@modifiedTime";
            paras[4].Value = item.ModifiedTime;
            return factory.ExecuteNonQuery(SqlInsertEntry, paras);

        }

        public int InsertEntry(int cateId, string title, string content, string user, DateTime time)
        {
            DbParameter[] paras = { DBFactory.Instance.CreateParameter(), 
                                      DBFactory.Instance.CreateParameter(), 
                                      DBFactory.Instance.CreateParameter(),
                                  DBFactory.Instance.CreateParameter(), 
                                  DBFactory.Instance.CreateParameter()};
            paras[0].ParameterName = "@category";
            paras[0].Value = cateId;

            paras[1].ParameterName = "@title";
            paras[1].Value = title;

            paras[2].ParameterName = "@content";
            paras[2].Value = content;

            paras[3].ParameterName = "@modifiedUser";
            paras[3].Value = user;

            paras[4].ParameterName = "@modifiedTime";
            paras[4].Value = time;
            return factory.ExecuteNonQuery(SqlInsertEntry, paras);

        }


        public int UpdateEntry(EntryItem item)
        {
            DbParameter[] paras = { DBFactory.Instance.CreateParameter(), 
                                      DBFactory.Instance.CreateParameter(), 
                                      DBFactory.Instance.CreateParameter(),
                                  DBFactory.Instance.CreateParameter(), 
                                  DBFactory.Instance.CreateParameter(),
                                  DBFactory.Instance.CreateParameter()};
            paras[0].ParameterName = "@category";
            paras[0].Value = item.Category.CategoryID;

            paras[1].ParameterName = "@title";
            paras[1].Value = item.Title;

            paras[2].ParameterName = "@content";
            paras[2].Value = item.Content;

            paras[3].ParameterName = "@modifiedUser";
            paras[3].Value = item.ModifiedUser;

            paras[4].ParameterName = "@modifiedTime";
            paras[4].Value = item.ModifiedTime;

            paras[5].ParameterName = "@id";
            paras[5].Value = item.ID;

            return factory.ExecuteNonQuery(SqlUpdateEntry, paras);
        }

        public int DeleteEntry(EntryItem item)
        {
            DbParameter[] paras = { DBFactory.Instance.CreateParameter()};
            paras[0].ParameterName = "@id";
            paras[0].Value = item.ID;

            return factory.ExecuteNonQuery(SqlDeleteEntry, paras);
        }
    }
}
